﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Globalization;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using MockPrjTeam4.Entity;
using MockPrjTeam4.Common;

namespace MockPrjTeam4.DAO
{
    public class DepartmentsDAO
    {
        /// <summary>
        /// Method to add a Department
        /// </summary>
        /// <param name="dep"></param>
        /// <returns></returns>
        public static bool AddDepartment(Departments dep)
        {
            string query = string.Format(CultureInfo.CurrentCulture,
                "INSERT INTO {0}({1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10},  " +
                "{11}, {12}, {13}, {14}, {15}, {16}, {17})" +
                "VALUES(@DepartmentName, @BusinessId, @ContactId, @ShortDescription, @FullDescription, " +
                "@CityTown, @County, @CountryId, @PhoneNumber, @Fax, @Email, @WebAddress, @IsActive, @PostCode, " +
                "@AddressLine1, @AddressLine2, @AddressLine3)", 
                ScheduleEntities.TableDepartments, 
                Departments.FieldDepartmentName, 
                Departments.FieldBusinessId, 
                Departments.FieldContactId, 
                Departments.FieldShortDescription, 
                Departments.FieldFullDescription, 
                Departments.FieldCityTown, 
                Departments.FieldCounty, 
                Departments.FieldCountryId, 
                Departments.FieldPhoneNumber, 
                Departments.FieldFax, 
                Departments.FieldEmail, 
                Departments.FieldWebAddress, 
                Departments.FieldIsActive, 
                Departments.FieldPostCode, 
                Departments.FieldAddressLine1,
                Departments.FieldAddressLine2,
                Departments.FieldAddressLine3);
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DepartmentName", dep.DepartmentName), 
                new SqlParameter("@BusinessId", dep.BusinessId), 
                new SqlParameter("@ContactId", dep.ContactId), 
                new SqlParameter("@ShortDescription", dep.ShortDescription), 
                new SqlParameter("@FullDescription", dep.FullDescription), 
                new SqlParameter("@CityTown", dep.CityTown), 
                new SqlParameter("@County", dep.County), 
                new SqlParameter("@CountryId", dep.CountryId), 
                new SqlParameter("@PhoneNumber", dep.PhoneNumber), 
                new SqlParameter("@Fax", dep.Fax), 
                new SqlParameter("@Email", dep.Email), 
                new SqlParameter("@WebAddress", dep.WebAddress), 
                new SqlParameter("@IsActive", dep.IsActive), 
                new SqlParameter("@PostCode", dep.PostCode), 
                new SqlParameter("@AddressLine1", dep.AddressLine1), 
                new SqlParameter("@AddressLine2", dep.AddressLine2), 
                new SqlParameter("@AddressLine3", dep.AddressLine3) 
            };
                SqlHelper.ExecuteNonQuery(query, parameter);
                return true;
        }

        /// <summary>
        /// Method to get an object Department
        /// </summary>
        /// <param name="read"></param>
        /// <returns></returns>
        public static Departments DepartmentsObject(IDataRecord read)
        {
            return new Departments
            {
                DepartmentId = Methods.ConvertNumber(read[Departments.FieldDepartmentId].ToString()),
                DepartmentName = Methods.ConvertString(read[Departments.FieldDepartmentName].ToString()),
                BusinessId = Methods.ConvertNumber(read[Departments.FieldBusinessId].ToString()),
                AddressId  = Methods.ConvertNumber(read[Departments.FieldAddressId].ToString()),
                ContactId = Methods.ConvertNumber(read[Departments.FieldContactId].ToString()),
                ShortDescription = Methods.ConvertString(read[Departments.FieldShortDescription].ToString()),
                FullDescription = Methods.ConvertString(read[Departments.FieldFullDescription].ToString()),
                CityTown = Methods.ConvertString(read[Departments.FieldCityTown].ToString()),
                County = Methods.ConvertString(read[Departments.FieldCounty].ToString()),
                CountryId = Methods.ConvertNumber(read[Departments.FieldCountryId].ToString()),
                PhoneNumber = Methods.ConvertString(read[Departments.FieldPhoneNumber].ToString()),
                Fax = Methods.ConvertString(read[Departments.FieldFax].ToString()),
                Email = Methods.ConvertString(read[Departments.FieldEmail].ToString()),
                WebAddress = Methods.ConvertString(read[Departments.FieldWebAddress].ToString()),
                DirectorateId = Methods.ConvertNumber(read[Departments.FieldDirectorateId].ToString()),
                IsActive = Methods.ConvertBoolean(read[Departments.FieldIsActive].ToString()),
                PostCode = Methods.ConvertString(read[Departments.FieldPostCode].ToString()),
                AddressLine1 = Methods.ConvertString(read[Departments.FieldAddressLine1].ToString()),
                AddressLine2 = Methods.ConvertString(read[Departments.FieldAddressLine2].ToString()),
                AddressLine3 = Methods.ConvertString(read[Departments.FieldAddressLine3].ToString()),
            };
        }

        /// <summary>
        /// Method to get a list of Departments
        /// </summary>
        /// <returns></returns>
        public static IList<Departments> GetListDepartments()
        {
            IList<Departments> dep = new List<Departments>();
            string query = string.Format("SELECT * FROM Departments");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                dep.Add(DepartmentsObject(read));
            }
            return dep;
        }

        public static IList<Departments> GetListActiveDepartments()
        {
            IList<Departments> dep = new List<Departments>();
            string query = string.Format("SELECT * FROM Departments WHERE IsActive='true'");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while (read.Read())
            {
                dep.Add(DepartmentsObject(read));
            }
            return dep;
        }

        public static Departments GetDepartmentById(int id)
        {
            string query = string.Format("SELECT * FROM Departments WHERE DepartmentId = @DepartmentId");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DepartmentId", id)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            while (read.Read())
            {
                return DepartmentsObject(read);
            }
            return null;
        }

        public static IList<Departments> Search(string range, string isActive)
        {
            IList<Departments> dep = new List<Departments>();
            string query = string.Format("SELECT * FROM Departments WHERE DepartmentName LIKE @DepName ORDER BY DepartmentName");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@DepName", range)
            };
            if (isActive == "true")
            {
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    dep.Add(DepartmentsObject(read));
                }
                return dep;
            }
            else
            {
                query = string.Format("SELECT * FROM Departments WHERE DepartmentName LIKE @DepName AND IsActive='true' ORDER BY DepartmentName");
                IDataReader read = SqlHelper.ExecuteReader(query, parameter);
                while (read.Read())
                {
                    dep.Add(DepartmentsObject(read));
                }
                return dep;
            }
        }
    }
}